package com.example.demo.sensitive.service.impl;

import com.example.demo.sensitive.entity.DatabaseConfig;
import com.example.demo.sensitive.service.DatabaseService;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.ConcurrentHashMap;

/**
 * 数据库查询服务
 * 专门处理动态数据库的查询操作
 */
@Service
public class DatabaseQueryService {

    private final ConcurrentHashMap<String, DataSource> dataSourceCache = new ConcurrentHashMap<>();

    /**
     * 查询指定数据库的表信息
     */
    public List<DatabaseService.TableInfo> getTablesByDatabase(DatabaseConfig dbConfig) {
        List<DatabaseService.TableInfo> tables = new ArrayList<>();
        String databaseName = extractDatabaseName(dbConfig.getUrl());
        
        String sql = """
            SELECT 
                TABLE_NAME as tableName,
                TABLE_COMMENT as tableComment,
                TABLE_ROWS as rowCount,
                ENGINE as engine,
                TABLE_COLLATION as charset,
                CREATE_TIME as createTime,
                UPDATE_TIME as updateTime,
                DATA_LENGTH as dataLength,
                INDEX_LENGTH as indexLength
            FROM information_schema.TABLES 
            WHERE TABLE_SCHEMA = ? 
            AND TABLE_TYPE = 'BASE TABLE'
            ORDER BY TABLE_NAME
            """;
            
        try (Connection connection = getConnection(dbConfig);
             PreparedStatement stmt = connection.prepareStatement(sql)) {
            
            stmt.setString(1, databaseName);
            
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    DatabaseService.TableInfo tableInfo = new DatabaseService.TableInfo();
                    tableInfo.setTableName(rs.getString("tableName"));
                    tableInfo.setTableComment(rs.getString("tableComment"));
                    tableInfo.setRowCount(rs.getLong("rowCount"));
                    tableInfo.setEngine(rs.getString("engine"));
                    tableInfo.setCharset(rs.getString("charset"));
                    tableInfo.setCreateTime(rs.getString("createTime"));
                    tableInfo.setUpdateTime(rs.getString("updateTime"));
                    tableInfo.setDataLength(rs.getLong("dataLength"));
                    tableInfo.setIndexLength(rs.getLong("indexLength"));
                    tables.add(tableInfo);
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("查询数据库表信息失败: " + e.getMessage(), e);
        }
        
        return tables;
    }

    /**
     * 查询指定数据库的表数量
     */
    public Integer getTableCount(DatabaseConfig dbConfig) {
        String databaseName = extractDatabaseName(dbConfig.getUrl());
        String sql = "SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = ? AND TABLE_TYPE = 'BASE TABLE'";
        
        try (Connection connection = getConnection(dbConfig);
             PreparedStatement stmt = connection.prepareStatement(sql)) {
            
            stmt.setString(1, databaseName);
            
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt(1);
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("查询表数量失败: " + e.getMessage(), e);
        }
        
        return 0;
    }

    /**
     * 测试数据库连接
     */
    public DatabaseService.ConnectionInfo testConnection(DatabaseConfig dbConfig) {
        String sql = "SELECT VERSION() as version, DATABASE() as currentDatabase, NOW() as currentTime";
        
        try (Connection connection = getConnection(dbConfig);
             PreparedStatement stmt = connection.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {
            
            if (rs.next()) {
                DatabaseService.ConnectionInfo info = new DatabaseService.ConnectionInfo();
                info.setVersion(rs.getString("version"));
                info.setCurrentDatabase(rs.getString("currentDatabase"));
                info.setCurrentTime(rs.getString("currentTime"));
                return info;
            }
        } catch (SQLException e) {
            throw new RuntimeException("测试连接失败: " + e.getMessage(), e);
        }
        
        return null;
    }

    /**
     * 获取数据库连接（使用连接池）
     */
    private Connection getConnection(DatabaseConfig dbConfig) throws SQLException {
        DataSource dataSource = getDataSource(dbConfig);
        return dataSource.getConnection();
    }

    /**
     * 获取数据源（带缓存）
     */
    private DataSource getDataSource(DatabaseConfig dbConfig) {
        String cacheKey = generateCacheKey(dbConfig);
        
        return dataSourceCache.computeIfAbsent(cacheKey, key -> {
            HikariConfig hikariConfig = new HikariConfig();
            hikariConfig.setJdbcUrl(dbConfig.getUrl());
            hikariConfig.setUsername(dbConfig.getUsername());
            hikariConfig.setPassword(dbConfig.getPassword());
            hikariConfig.setDriverClassName(dbConfig.getDriverClassName());
            
            // 连接池配置
            hikariConfig.setMaximumPoolSize(5);
            hikariConfig.setMinimumIdle(1);
            hikariConfig.setConnectionTimeout(10000);
            hikariConfig.setIdleTimeout(300000);
            hikariConfig.setMaxLifetime(900000);
            hikariConfig.setLeakDetectionThreshold(60000);
            
            return new HikariDataSource(hikariConfig);
        });
    }

    /**
     * 从URL中提取数据库名称
     */
    private String extractDatabaseName(String url) {
        String[] parts = url.split("/");
        if (parts.length < 4) {
            throw new RuntimeException("无效的数据库URL格式");
        }
        String dbPart = parts[3];
        if (dbPart.contains("?")) {
            dbPart = dbPart.substring(0, dbPart.indexOf("?"));
        }
        return dbPart;
    }

    /**
     * 生成缓存键
     */
    private String generateCacheKey(DatabaseConfig dbConfig) {
        return String.format("%s_%s_%s", 
            dbConfig.getUrl(), 
            dbConfig.getUsername(), 
            dbConfig.getDriverClassName());
    }

    /**
     * 清理缓存
     */
    public void clearCache() {
        dataSourceCache.values().forEach(dataSource -> {
            if (dataSource instanceof HikariDataSource) {
                ((HikariDataSource) dataSource).close();
            }
        });
        dataSourceCache.clear();
    }
}
